package com.ycdx.zwzc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;

import com.ycdx.zwzc.pojo.User;
import com.ycdx.zwzc.utils.DBPool;

public class UserImpl {
	static Logger logger = Logger.getLogger(UserImpl.class.getName());
	/**
	 * 根据帐号密码判断用户是否合法
	 * @param username
	 * @param password
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static boolean isLeagal(String username,String password){
		//System.out.println(password);
		boolean result = false;
		String sql = "select * from tb_user where username=? and password=?";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {username,password};
		try {
			User tempUser = (User) qr.query(conn, sql, new BeanHandler(User.class), params);
			if(tempUser!=null){
				//合法用户
				result = true;
			}
		}  catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-isLeagal()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-isLeagal()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;		
	}
	
	/**
	 * 根据帐号密码获取用户
	 * @param username
	 * @param password
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static User getUser(String username,String password){
		User retUser = null;
		String sql = "select * from tb_user where username=? and password=?";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {username,password};		
		try {
			retUser = (User) qr.query(conn, sql, new BeanHandler(User.class), params);			
		}  catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-getUser()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-getUser()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return retUser;		
	}
	
	@SuppressWarnings("unchecked")
	public static List<User> getAllCommonUser(){
		List<User> userList = new ArrayList<User>();
		String sql = "select * from tb_user where role=3;";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		try {
			userList = (List<User>) qr.query(conn, sql, new BeanListHandler(User.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-getAllCommonUser()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-getAllCommonUser()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return userList;
	}
	
	/**
	 * 添加入库
	 * @param req
	 * @return
	 */
	public static int addUser(User user){
		String sql = "insert into tb_user(" +
				"username,password,name,department,role) values(" +
				"?,?,?,?,?);";	
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {user.getUsername(),user.getPassword(),user.getName(),user.getDepartment(),"3"};	
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-addUser()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-addUser()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	/**
	 * 根据ID获取指定对象
	 * @param id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static User getUserById(String id){
		//System.out.println(id);
		String sql = "select * from tb_user where id=?";
		
		User user = new User();
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {id};		
		try {
			user = (User) qr.query(conn, sql, new BeanHandler(User.class), params);			
		}  catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-getUserById()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-getUserById()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return user;
	}
	
	/**
	 * 更新指定对象（没有修改密码）
	 * @param reason
	 * @return
	 */
	public static int update(User user){
		String sql = "update tb_user set username=?,name=?,department=? where id=?";
		
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {user.getUsername(),user.getName(),user.getDepartment(),user.getId()};	
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-update()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-update()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return flag;
	}
	
	/**
	 * 更新指定对象（修改密码）
	 * @param reason
	 * @return
	 */
	public static int updateWithPassword(User user){
		String sql = "update tb_user set username=?,password=?,name=?,department=? where id=?";
		
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {user.getUsername(),user.getPassword(),user.getName(),user.getDepartment(),user.getId()};	
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-updateWithPassword()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-updateWithPassword()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return flag;
	}
	
	/**
	 * 删除指定对象
	 * @param reason
	 * @return
	 */
	public static int deleteUser(String id){
		String sql = "delete from tb_user where id=?";
		
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {id};
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-deleteUser()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-deleteUser()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	
}
